/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package model.util;
import java.sql.*;
import java.util.*;
/**
 *
 * @author L_N_S_Binh
 * @version 1.0
 */
public class DataHelper
{
	
    private static String connectionString ;
    private static String user = "user" ;
    private static String pass = "root";
    
    /**
     * @return the connectionString
     */
    public static String getConnectionString() {
        return connectionString;
    }

    /**
     * @param aConnectionString the connectionString to set
     */
    public static void setConnectionString(String aConnectionString) {
        connectionString = aConnectionString;
    }

    /**
     * @return the user
     */
    public static String getUser() {
        return user;
    }

    /**
     * @param aUser the user to set
     */
    public static void setUser(String aUser) {
        user = aUser;
    }

    /**
     * @return the pass
     */
    public static String getPass() {
        return pass;
    }

    /**
     * @param aPass the pass to set
     */
    public static void setPass(String aPass) {
        pass = aPass;
    }
    
    private  Connection connection;
    private Properties info ;
    
    public DataHelper(){
////
        this.info = new Properties() ;
        info.setProperty("characterEncoding","utf8");
        info.setProperty(user , pass);
    }
      
    public Connection getConnection() {
////     
        return connection;
    }

    public boolean open() {
////      
        try
        {
            //Dang ky driver
            DriverManager.registerDriver(new org.gjt.mm.mysql.Driver());
            //Lay ket noi
            this.connection = DriverManager.getConnection(connectionString, this.info);
            return true ;
        }
        catch(SQLException ex)
        {
            return false ;
        }
    }

    public void close() {
////       
      try 
      {
         this.connection.close();
      }
      catch (SQLException ex) 
      {
          //Logger.getLogger(MySqlDataAccessHelper.class.getName()).log(Level.SEVERE, null, ex);
      }
    }

    public ResultSet executeQuery(String sql) {
////        
      ResultSet rs = null;
      try 
      {
          Statement sm = this.connection.createStatement();
          rs = sm.executeQuery(sql);
      } 
      catch (SQLException ex) 
      {
         //System.out.println(ex.getMessage());
      }
      return rs;
    }

    public boolean executeUpdate(String sql) {
////      
        int num = -1;
        this.open();
        try 
        {
              Statement sm = this.connection.createStatement();
              num = sm.executeUpdate(sql);
        } 
        catch (SQLException ex) 
        {
            //System.out.println(ex.getMessage());
        }
        
        this.close();
        
        if(num > -1)
            return true ;
        return false ;
    }
    
    public static ResultSet executeQuery(String sql , Connection cnn) {
////        
      ResultSet rs = null;
      try 
      {
          Statement sm = cnn.createStatement();
          rs = sm.executeQuery(sql);
      } 
      catch (SQLException ex) 
      {
         //System.out.println(ex.getMessage());
      }
      return rs;
    }
    
    public static boolean executeUpdate(String sql , Connection cnn) {
////      
        int num = -1;
        try 
        {
              Statement sm = cnn.createStatement();
              num = sm.executeUpdate(sql);
        } 
        catch (SQLException ex) 
        {
              //System.out.println(ex.getMessage());
        }
        
        if(num > -1)
            return true ;
        return false ;
    }
    
    /**
     * goi thuc thi stored tra ra 1 result set
     */
    public ResultSet executeProcedure(String procName, ArrayList<Object> para ) throws SQLException
    {
        ResultSet rs = null;
        try
        {
            CallableStatement sm = connection.prepareCall(procName) ;
            for(int i =0;i<para.size();i++)
            {
                sm.setObject((i+1), para.get(i));
            }
            sm.execute();
            rs = sm.getResultSet();
            //sm.registerOutParameter(procName, sqlType);
        }catch(SQLException ex)
        {
            throw ex;
        }
        return rs;
    }
    /**
     * goi thuc thi stored tra ra so dong bi thay doi
     */
    public int executeInsProcedure(String procName,ArrayList<Object> paraIn ) throws SQLException
    {
        int kq =-1;
        try
        {
            CallableStatement sm = connection.prepareCall(procName) ;
            for(int i =0;i<paraIn.size();i++)
            {
                sm.setObject((i+1), paraIn.get(i));
            }
            kq = sm.executeUpdate();
        }catch(SQLException ex)
        {
            throw ex;
        }
        return kq;
    }
}
